package com.ddzh.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ddzh.pojo.Member;
import com.ddzh.vo.RptDateVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface MemberMapper extends BaseMapper<Member> {

    @Select("SELECT age_temp AS name,COUNT(*) AS value FROM (\n" +
            "SELECT age,\n" +
            "CASE\n" +
            "WHEN age BETWEEN 0 AND 18 THEN '0-18'\n" +
            "WHEN age BETWEEN 18 AND 30 THEN '18-30'\n" +
            "WHEN age BETWEEN 30 AND 45 THEN '30-45'\n" +
            "WHEN age BETWEEN 45 AND 60 THEN '45-60'\n" +
            "WHEN age >60 THEN '60+'\n" +
            "END AS age_temp\n" +
            "FROM member\n" +
            ")m_member GROUP BY age_temp")
    public List<Map> findMemberAmountByAge();

    @Select("SELECT  sexname AS name, COUNT(*) AS value FROM (\n" +
            "SELECT sex,\n" +
            "CASE \n" +
            "WHEN sex =1 THEN '男'\n" +
            "WHEN sex = 2 THEN '女'\n" +
            "END AS sexname\n" +
            "FROM member)s_member GROUP BY sexname")
    public List<Map> findMemberAmountBySex();


    @Select("SELECT r.fyear AS fyear,r.fmonth AS fmonth,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN DATE_FORMAT(NOW(),'%Y%m%d')-10000  AND DATE_FORMAT(NOW(),'%Y%m%d')\n" +
            "GROUP BY r.fyear,r.fmonth")
    public List<RptDateVo> findAmounByYear();


    //按照星期查询
    @Select("SELECT r.fmonth AS fmonth,r.fweek AS fweek,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN #{start} AND #{end}\n" +
            "GROUP BY r.fmonth,r.fweek")
    public List<RptDateVo> findDateByWeek(@Param("start") long start, @Param("end") long end );

    //按月查询
    @Select("SELECT r.fmonth AS fmonth,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN #{start} AND #{end}\n" +
            "GROUP BY r.fmonth")
    public List<RptDateVo> findDateByMonth(@Param("start") long start,@Param("end") long end );


    @Select("SELECT r.fyear AS fyear,r.fquarter AS fquarter,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN #{start} AND #{end}\n" +
            "GROUP BY r.fyear,r.fquarter")
    public  List<RptDateVo> findDateByQuarter(@Param("start") long start,@Param("end") long end );

    @Select("SELECT r.fyear AS fyear,COUNT(m.id) AS total\n" +
            "FROM rpt_date r \n" +
            "LEFT JOIN member m ON r.id = m.create_date_id\n" +
            "WHERE r.id BETWEEN #{start} AND #{end}\n" +
            "GROUP BY r.fyear")
    public  List<RptDateVo> findDateByYear(@Param("start") long start,@Param("end") long end );
}
